<?php

namespace App\Http\Controllers;

use App\Models\Periodical;
use App\Models\PeriodicalPrice;
use App\Models\PublicationHistory;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use function PHPUnit\Framework\isNull;

class PeriodicalController extends Controller
{
    //
    public function getList()
    {
        $validator = validator(\request()->all(), [
            'keyword' => 'nullable|string',
            'direction' => 'nullable|array',
            'included_database' => 'nullable|array',
            'level' => 'nullable|array',
            'cycle' => 'nullable|array',
            'year' => 'nullable|array',
            'page_range' => 'nullable|array',
            'audit_standard' => 'nullable|array',
            'invoice' => 'nullable|in:0,1',
            'suspend_receiving' => 'nullable|in:0,1',
            'science' => 'nullable|in:0,1',
            'post_code' => 'nullable|in:0,1',
            'e_number' => 'nullable|array',
            'check' => 'nullable|in:0,1',
            'cover_core' => 'nullable|in:0,1',
            'impact_factor' => 'nullable|in:0,1',
            'factor_range' => 'nullable|array|numeric',
            'date_y' => 'nullable|numeric',
            'date_m' => 'nullable|numeric',
            'date_type' => 'nullable|numeric',
            'recommend' => 'nullable|in:0,1',
            'page' => 'required|numeric',
            'limit' => 'required|numeric',
            'science_periodical' => 'nullable|in:0,1',
            'login'=>'nullable|numeric'
        ]);
        
        if ($validator->fails()) {
            return api_error('002');
        }
        
        if(\request('login') == 1){
            return api_error('login');
            $count = 4;
            $query = Periodical::with([
                'directions:id,name',
                'included_databases:id,name',
                'audit_standard:id,name',
                'level:id,name',
                'cycle:id,name',
                'publication_time:id,name',
                'current',
                'history',
                'sort_price'
            ]);
            $data = $query->forPage(1, 4)
            ->where('suspend_receiving',1)
            ->get()
            ->map(function ($item) {
                $fixed_current = [
                    'id' => $item->current['id'],
                    'content' => $item->current['content'],
                    'data' => $item->current['content'],
                    'publish_at' => date('Y-m',$item->current['publish_at']),
                    'publish_at_about' => $item->current['publish_at_about'],
                    'publish_at_about_name' => Db::table('publication_times')->where('id',$item->current['publish_at_about'])->value('name')
                ];
                return [
                    'id' => $item->id,
                    'name' => $item->name,
                    'cover' => $item->cover,
                    'audit_standard_id' => $item->audit_standard_id,
                    'audit_standard' => $item->audit_standard->name ?? '',
                    'direction' => $item->directions->pluck('name') ?? '',
                    'level_id' => $item->level_id,
                    'level' => $item->level->name ?? '',
                    'cycle_id' => $item->cycle_id,
                    'cycle' => $item->cycle->name ?? '',
                    'date' => $item->publication_date_y.'-'.$item->publication_date_m,
                    'date_type_id' => $item->publication_time_id,
                    'date_type' => $item->publication_time->name ?? '',
                    'included_database' => $item->included_databases->pluck('name') ?? '',
                    'current' => $fixed_current,
                    'letter' => $item->letter,
                    'recommend' => $item->recommend,
                    'annex_info'=>$item->annex_info,
                    'sort_price' => $item->sort_price,
                    'e_number' => $item->e_number
                ];
            });
            return api_output([
                'count' => $count,
                'data' => $data
            ]);
        }

        $keyword = \request('keyword');
        $direction = \request('direction');
        $included_database = \request('included_database');
        $level = \request('level');
        $cycle = \request('cycle');
        $year = \request('year');
        $page_range = \request('page_range');
        $audit_standard = \request('audit_standard');
        $invoice = \request('invoice');
        $suspend_receiving = \request('suspend_receiving');
        $science = \request('science');
        $post_code = \request('post_code');
        $e_number = \request('e_number');
        $check = \request('check');
        $cover_core = \request('cover_core');
        $impact_factor = \request('impact_factor');
        $factor_range = \request('factor_range');
        $date_y = \request('date_y');
        $date_m = \request('date_m');
        $date_type = \request('date_type');
        $recommend = \request('recommend');
        $page = \request('page');
        $limit = \request('limit');
        $sort = \request('sort', 'sort');
        $science_periodical = \request('science_periodical');

        $e_number_where = [];
        if(is_array($e_number) && count($e_number)>0){
            foreach ($e_number as $e_number_item){
                $e_number_where[] = $e_number_item+0;
            }
        }

        $query = Periodical::with([
            'directions:id,name',
            'included_databases:id,name',
            'audit_standard:id,name',
            'level:id,name',
            'cycle:id,name',
            'publication_time:id,name',
            'current',
            'history',
            'sort_price'
        ])
            ->when($keyword, function ($query, $keyword) {
                // return $query->whereRaw("concat('name','annex_info','column','organizer','management') like '%".$keyword."%'");
                // return $query->where('name', 'like', "%$keyword%");
                                // ->orWhere('annex_info','like',"%$keyword%")
                                // ->orWhere('column','like',"%$keyword%")
                                // ->orWhere('organizer','like',"%$keyword%")
                                // ->orWhere('management','like',"%$keyword%");
                return $query->where(function($query) use ($keyword){
                    $query->where('name', 'like', "%$keyword%")
                        ->orWhere('annex_info','like',"%$keyword%")
                        ->orWhere('column','like',"%$keyword%")
                        ->orWhere('organizer','like',"%$keyword%")
                        ->orWhere('management','like',"%$keyword%");
                });
            })
            ->when($direction, function ($query, $direction) {
                return $query->whereHas('directions', function ($query) use ($direction) {
                    $query->whereIn('directions.id', $direction);
                });
            })
            ->when($included_database, function ($query, $included_database) {
                return $query->whereHas('included_databases', function ($query) use ($included_database) {
                    $query->whereIn('included_databases.id', $included_database);
                });
            })
            ->when($level, function ($query, $level) {
                return $query->whereIn('level_id', $level);
            })
            ->when($cycle, function ($query, $cycle) {
                return $query->whereIn('cycle_id', $cycle);
            })
            ->when($year, function ($query, $year) {
                return $query->where('included_date', $year);
            })
            ->when($page_range, function ($query, $page_range) {
                foreach ($page_range as $index => $item) {
                    if ($index == 0) {
                        $query->whereBetween('page', [$item - 100, $item]);
                    } else {
                        $query->orWhereBetween('page', [$item - 100, $item]);
                    }
                }
                return $query;
            })
            ->when($audit_standard, function ($query, $audit_standard) {
                return $query->whereIn('audit_standard_id', $audit_standard);
            })
            ->when($invoice, function ($query, $invoice) {
                return $query->where('invoice', $invoice);
            })
            ->when($recommend, function ($query, $recommend) {
                return $query->where('recommend', $recommend);
            })
            ->when($suspend_receiving, function ($query, $suspend_receiving) {
                return $query->where('suspend_receiving', $suspend_receiving);
            })
            ->when($science_periodical, function ($query,$science_periodical) {
                return $query->where('science_periodical',$science_periodical);
            })
            ->when($science, function ($query,$science) {
                return $query->where('science_periodical',$science);
            })
            ->when($post_code, function ($query, $post_code) {
                if($post_code == '1'){
                    return $query->whereNotNull('post_code');
                }
                else{
                    return $query;
                }
                
            })
            ->when($e_number_where, function ($query, $e_number_where) {
                return $query->whereIn('e_number',$e_number_where);
            })
            ->when($check, function ($query, $check) {
                return $query->where('check_manuscript', $check);
            })
            ->when($cover_core, function ($query, $cover_core) {
                return $query->where('cover_core', $cover_core);
            })
            ->when($impact_factor, function ($query, $impact_factor) {
                if ($impact_factor) {
                    return $query->whereNotNull('impact_factor');
                }
            })
            ->when($factor_range, function ($query, $factor_range) {
                foreach ($factor_range as $index => $item) {
                    if ($index == 0) {
                        if ($item == 0.3) {
                            $query->where('impact_factor', '<', 0.3);
                        } else {
                            $query->whereBetween('impact_factor', [$item - 0.1, $item]);
                        }
                    } else {
                        if ($item == 0.3) {
                            $query->orWhere('impact_factor', '<', 0.3);
                        } else {
                            $query->orWhereBetween('page', [$item - 100, $item]);
                        }
                    }
                }
            })
            ->when($date_y, function ($query, $date_y) {
                return $query->where('publication_date_y', $date_y);
            })
            ->when($date_m, function ($query, $date_m) {
                return $query->where('publication_date_m', $date_m);
            })
            ->when($date_type, function ($query, $date_type) {
                return $query->where('publication_time_id', $date_type);
            });
     
        $count = $query->count();

        $data = $query->forPage($page, $limit)
            ->where('suspend_receiving',1)
            ->orderByRaw('case when name like "%'.$keyword.'%" then 0 else 1 end asc')
            ->when($sort == 'sort', function ($query){
                return $query->orderBy('sort','desc');
            })
            ->when($sort == 'price', function ($query){
                return $query->orderBy('sort_price','asc');
            })
            ->orderBy('publication_date_y','asc')
            ->orderBy('publication_date_m','asc')
            ->orderBy('publication_time_id','asc')
            ->get()
            ->map(function ($item) {
                $fixed_current = [
                    'id' => $item->current['id'],
                    'content' => $item->current['content'],
                    'data' => $item->current['content'],
                    'publish_at' => date('Y-m',$item->current['publish_at']),
                    'publish_at_about' => $item->current['publish_at_about'],
                    'publish_at_about_name' => Db::table('publication_times')->where('id',$item->current['publish_at_about'])->value('name')
                ];
                return [
                    'id' => $item->id,
                    'name' => $item->name,
                    'cover' => $item->cover,
                    'audit_standard_id' => $item->audit_standard_id,
                    'audit_standard' => $item->audit_standard->name ?? '',
                    'direction' => $item->directions->pluck('name') ?? '',
                    'level_id' => $item->level_id,
                    'level' => $item->level->name ?? '',
                    'cycle_id' => $item->cycle_id,
                    'cycle' => $item->cycle->name ?? '',
                    'date' => $item->publication_date_y.'-'.$item->publication_date_m,
                    'date_type_id' => $item->publication_time_id,
                    'date_type' => $item->publication_time->name ?? '',
                    'included_database' => $item->included_databases->pluck('name') ?? '',
                    'current' => $fixed_current,
                    'letter' => $item->letter,
                    'recommend' => $item->recommend,
                    'annex_info'=>$item->annex_info,
                    'sort_price' => $item->sort_price,
                    'e_number' => $item->e_number
                ];
            });


        return api_output([
            'count' => $count,
            'data' => $data
        ]);
    }


    public function baseInfo()
    {
        $validator = validator(\request()->all(), [
            'id' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return api_error('002');
        }

        $id = \request('id');

        $periodical = Periodical::with([
            'directions:id,name',
            'included_databases:id,name',
            'audit_standard:id,name',
            'level:id,name',
            'cycle:id,name',
            'publication_time:id,name',
            'current:periodical_id,content',
            'price:id,periodical_id,role_id,price',
            'price.role'
        ])
            ->where('id', $id)
            ->first();

        if (is_null($periodical)) {
            return api_error('006');
        }

        if (auth()->check()) {
            if (!is_null($periodical->price)) {
                $periodical->price->transform(function ($item) {
                    return [
                        'id' => $item->id,
                        'periodical_id' => $item->periodical_id,
                        'role_id' => $item->role_id,
                        'price' => $item->price,
                        'role' => $item->role->name
                    ];
                });
            }

            $data = [
                'id' => $periodical->id,
                'name' => $periodical->name,
                'included_database' => $periodical->included_databases->pluck('name') ?? '',
                'char_number' => $periodical->char_count,
                'remark' => $periodical->remark,
                'level_id' => $periodical->level_id,
                'level' => $periodical->level->name ?? '',
                'price' => $periodical->price,
                'current' => $periodical->current->content ?? '',
            ];
        } else {
            //未登录则不返回价格信息
            $data = [
                'id' => $periodical->id,
                'name' => $periodical->name,
                'included_database' => $periodical->included_databases->pluck('name') ?? '',
                'char_number' => $periodical->char_count,
                'remark' => $periodical->remark,
                'level_id' => $periodical->level_id,
                'level' => $periodical->level->name ?? '',
                'current' => $periodical->current->content ?? '',
            ];
        }
        return api_output($data);
    }

    public function detail()
    {

        $validator = validator(\request()->all(), [
            'id' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return api_error('002');
        }

        $id = \request('id');

        $periodical = Periodical::with([
            'directions:id,name',
            'included_databases:id,name',
            'audit_standard:id,name',
            'level:id,name',
            'cycle:id,name',
            'publication_time:id,name',
            'current',
            'price:id,periodical_id,role_id,price',
            'price.role',
            'history',
            'favorite' => function ($query) {
                $query->where('user_id', auth()->id());
            }
        ])
            ->where('id', $id)
            ->first();

        if (is_null($periodical)) {
            return api_error('006');
        }

        if (!is_null($periodical->price)) {
            $periodical->price->transform(function ($item) {
                return [
                    'id' => $item->id,
                    'periodical_id' => $item->periodical_id,
                    'role_id' => $item->role_id,
                    'price' => $item->price,
                    'role' => $item->role->show_title
                ];
            });
        }

        if (!is_null($periodical->history)) {
            $periodical->history->transform(function ($item) {
                return [
                    'id' => $item->id,
                    // 'date' => Carbon::parse($item->created_at)->timestamp,
                    'created_at'=>Carbon::parse($item->created_at)->year,
                    'date'=>$item->publish_at,
                    'data' => $item->content,
                    'publish_at' => date('Y-m',$item->publish_at),
                    'publish_at_about' => $item->publish_at_about,
                    'publish_at_about_name' => Db::table('publication_times')->where('id',$item->publish_at_about)->value('name')
                ];
            });
        }
        $fixed_current = [
            'id' => $periodical->current['id'],
            'content' => $periodical->current['content'],
            'data' => $periodical->current['content'],
            'publish_at' => date('Y-m',$periodical->current['publish_at']),
            'publish_at_about' => $periodical->current['publish_at_about'],
            'publish_at_about_name' => Db::table('publication_times')->where('id',$periodical->current['publish_at_about'])->value('name')
        ];

        $data = [
            'id' => $periodical->id,
            'name' => $periodical->name,
            'cover' => $periodical->cover,
            'audit_standard_id' => $periodical->audit_standard_id,
            'audit_standard' => $periodical->audit_standard->name ?? '',
            'direction' => $periodical->directions->pluck('name') ?? '',
            'level_id' => $periodical->level_id,
            'level' => $periodical->level->name ?? '',
            'cycle_id' => $periodical->cycle_id,
            'cycle' => $periodical->cycle->name ?? '',
            'date' => $periodical->publication_date_y.'-'.$periodical->publication_date_m,
            'date_type_id' => $periodical->publication_time_id,
            'date_type' => $periodical->publication_time->name ?? '',
            'included_database' => $periodical->included_databases->pluck('name') ?? '',
            'current' => $fixed_current,
            'letter' => $periodical->letter,
            'science_periodical' => $periodical->science_periodical,
            'history' => $periodical->history,
            'price' => $periodical->price,
            'domestic_number' => $periodical->domestic_number,
            'international_number' => $periodical->international_number,
            'post_code' => $periodical->post_code,
            'char_count' => $periodical->char_count,
            'organizer' => $periodical->organizer,
            'management' => $periodical->management,
            'cover_core' => $periodical->cover_core,
            'impact_factor' => $periodical->impact_factor,
            'simple' => $periodical->simple,
            'page' => $periodical->page,
            'page_remark' => $periodical->page_remark,
            'postcode' => $periodical->postcode,
            'summary_keywords' => $periodical->summary_keywords,
            'author_info' => $periodical->author_info,
            'references' => $periodical->author_info,
            'review_difficulty' => $periodical->review_difficulty,
            'review_time' => $periodical->review_time,
            'column' => $periodical->column,
            'remark' => $periodical->remark,
            'favorite' => $periodical->favorite->count() !== 0 ? 1 : 0,
            'annex_info'=>$periodical->annex_info
        ];
        
        // 普通会员不配看价格
        $user = auth()->user();
        if($user->role_id == '1'){
            $data['price'] = [];
        }
        return api_output($data);
    }

    public function current()
    {
        $validator = validator(\request()->all(), [
            'page' => 'required|integer',
            'limit' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return api_error('002');
        }

        $page = \request('page');
        $limit = \request('limit');

        $count = PublicationHistory::all()->count();
        $data = PublicationHistory::all()
            // ->orderByDesc('publish_at')
            // ->orderByDesc('publish_at_about')
            // 20220809 修改为内容开头的日期排序(就是当天填写资料的日期)
            ->orderByDesc('created_at')
            ->values()
            ->forPage($page, $limit)
            ->transform(function ($item) {
                // return [
                //     'id' => $item->id,
                //     'data' => $item->content,
                // ];
                return [
                    'id' => $item->id,
                    'content' => $item->content,
                    'data' => $item->content,
                    'publish_at' => date('Y-m',$item->publish_at),
                    'publish_at_about' => $item->publish_at_about,
                    'publish_at_about_name' => Db::table('publication_times')->where('id',$item->publish_at_about)->value('name')
                ];
            });

        return api_output([
            'total' => $count,
            'data' => $data
        ]);
    }

    public function favorite()
    {
        $validator = validator(\request()->all(), [
            'id' => 'required|integer',
            'type' => 'required|in:0,1',
        ]);
        if ($validator->fails()) {
            return api_error('002');
        }

        $id = \request('id');
        $type = \request('type');
        $user = auth()->user();
        if ($type) {
            $periodical = Periodical::where('id', $id)->first();
            if (is_null($periodical)) {
                return api_error('006');
            }
            if (!is_null($user->favorite()->where('periodical_id', $id)->first())) {
                return api_error('007');
            }
            $user->favorite()->attach($id);
        } else {
            if (is_null($user->favorite()->where('periodical_id', $id)->first())) {
                return api_error('008');
            }
            $user->favorite()->detach($id);
        }
        return api_output(true);
    }

    public function favoriteList()
    {
        $validator = validator(\request()->all(), [
            'page' => 'required|integer',
            'limit' => 'required|integer',
        ]);
        if ($validator->fails()) {
            return api_error('002');
        }

        $page = \request('page');
        $limit = \request('limit');
        $user = auth()->user();

        $count = $user->favorite()->count();

        $data = $user->favorite()
            ->with([
                'directions:id,name',
                'included_databases:id,name',
                'audit_standard:id,name',
                'level:id,name',
                'cycle:id,name',
                'publication_time:id,name',
                'current'
            ])
            ->forPage($page, $limit)
            ->get()
            ->map(function ($item) {
                $fixed_current = [
                    'id' => $item->current['id'],
                    'content' => $item->current['content'],
                    'data' => $item->current['content'],
                    'publish_at' => date('Y-m',$item->current['publish_at']),
                    'publish_at_about' => $item->current['publish_at_about'],
                    'publish_at_about_name' => Db::table('publication_times')->where('id',$item->current['publish_at_about'])->value('name')
                ];
                return [
                    'id' => $item->id,
                    'name' => $item->name,
                    'cover' => $item->cover,
                    'audit_standard_id' => $item->audit_standard_id,
                    'audit_standard' => $item->audit_standard->name ?? '',
                    'direction' => $item->directions->pluck('name') ?? '',
                    'level_id' => $item->level_id,
                    'level' => $item->level->name ?? '',
                    'cycle_id' => $item->cycle_id,
                    'cycle' => $item->cycle->name ?? '',
                    'date' => $item->publication_date_y.'-'.$item->publication_date_m,
                    'date_type_id' => $item->publication_time_id,
                    'date_type' => $item->publication_time->name ?? '',
                    'included_database' => $item->included_databases->pluck('name') ?? '',
                    'current' => $fixed_current,
                    'letter' => $item->letter
                ];
            });


        return api_output([
            'count' => $count,
            'data' => $data
        ]);
    }


    public function publishStatus()
    {
        $now = Carbon::now();
        $dates = ["'".$now->format('Y/m')."'"];
        for ($i = 1; $i <= 6; $i++){
            $date = $now->addMonth(1);
            $dates[] = "'".$date->format('Y/m')."'";
        }
        $dateQuery = "(".implode(',',$dates).")";

        $countInfo = DB::table('periodicals')
            ->selectRaw("count(id) as count, concat(concat(publication_date_y, '/'),publication_date_m) as publish_date")
            ->havingRaw("publish_date in $dateQuery")
            ->groupBy('publish_date')
            ->get();

        $data = [];
        $now = Carbon::now()->subMonth(1);
        for ($i = 0; $i <= 6; $i++){
            $date = $now->addMonth(1);
            $data[] = [
                'date' => $date->format('Y/m'),
                'count' => $countInfo->firstWhere('publish_date', $date->format('Y/m'))->count ?? 0
            ];
        }

        return api_output($data);
    }
}
